package com.tm.controller;

import org.apache.poi.xssf.usermodel.*;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.*;
import java.util.ArrayList;

/**
 * @author huangp
 * @create 2021-09-20 16:16
 */
public class POIFZ {

    public static void getTableName(Class tClass){
        String name = tClass.getSimpleName();
        char[] chars = name.toCharArray();
        StringBuffer sb = new StringBuffer("");
        int a=0;
        for (int i = 0; i < chars.length; i++) {
            if (Character.isUpperCase(chars[i])){
                if (a==0){
                    chars[i] = Character.toLowerCase(chars[i]);
                    sb.append("t").append("_").append(chars[i]);
                    a++;
                }else {
                    chars[i] = Character.toLowerCase(chars[i]);
                    sb.append("_").append(chars[i]);
                }
            }else {
                sb.append(chars[i]);
            }

        }
        OutputStream(sb.toString());
    }
    public static void OutputStream(String tableName) {
        //1、创建excel表格文件
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        //2、创建sheet页面
        XSSFSheet sheet = xssfWorkbook.createSheet(tableName);
        XSSFRow row = sheet.createRow(0);
        // 创建data日期格式
        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();//通过文件来创建样式对象
        XSSFDataFormat dataFormat = xssfWorkbook.createDataFormat();//通过文件来创建一个日期格式
        short format = dataFormat.getFormat("yyyy-MM-dd HH:mm:ss");//通过格式对象，得到日期格式的short数据
        cellStyle.setDataFormat(format);//将日期格式的short数据赋值给样式对象
        //创建time日期格式
        XSSFCellStyle cellStyle1 = xssfWorkbook.createCellStyle();
        XSSFDataFormat dataFormat1 = xssfWorkbook.createDataFormat();
        short format1 = dataFormat1.getFormat("HH:mm:ss");
        cellStyle1.setDataFormat(format1);
        //拼写sql查询语句
        StringBuffer sql = new StringBuffer(" select * from "+tableName+" ");

        Connection conn = null;  //创建一个引用
        Statement st = null;
        ResultSet rs=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/tm4sx?serverTimezone=UTC&useSSL=false";
            //新版的mysql驱动jar包要设置：数据库名，时区，使用ssl等等
            String username = "root";//用户名
            String password = "801404";//密码
            conn = (Connection) DriverManager.getConnection(url, username, password);
            st = conn.createStatement();//在此处传入进行预编译
            rs = st.executeQuery(sql.toString());
            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                String columnName = metaData.getColumnName(i+1);
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(columnName);
            }
            ArrayList<Object> objects = new ArrayList<>();
            int rowCount=1;
            while (rs.next()){
                XSSFRow row1 = sheet.createRow(rowCount);
                rowCount++;
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    System.out.println(metaData.getColumnClassName(i));
                    if (metaData.getColumnClassName(i).contains("Timestamp")){
                        XSSFCell cell = row1.createCell(i - 1);
                        cell.setCellValue(rs.getDate(i));
                        cell.setCellStyle(cellStyle);
                    }
                    else if (metaData.getColumnClassName(i).contains("Time")){
                        XSSFCell cell = row1.createCell(i - 1);
                        cell.setCellValue(rs.getTime(i));
                        cell.setCellStyle(cellStyle1);
                    }
                    else if (metaData.getColumnClassName(i).contains("String")){
                        row1.createCell(i-1).setCellValue(rs.getString(i));
                    }
                    else if (metaData.getColumnClassName(i).contains("Integer")){
                        row1.createCell(i-1).setCellValue(rs.getInt(i));
                    }
                }
            }
            xssfWorkbook.write(new FileOutputStream("C:\\Users\\Administrator\\Desktop\\aaa.xlsx"));
            try {
                conn = (Connection) DriverManager.getConnection(url, username, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            if (null!=rs){
                rs.close();
            }
            if (null!=st){
                st.close();
            }
            if (null!=conn){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
